USE [GD2C2014]
GO

--creacion esquema del grupo
if not exists(select * from sys.schemas where name = 'WE_MISS_THE_GARBAGE_COLLECTOR')
execute('CREATE SCHEMA [WE_MISS_THE_GARBAGE_COLLECTOR] AUTHORIZATION [gd]')

else print('No te creo nada')
GO

/**************************************
				ROL
**************************************/

CREATE TABLE [WE_MISS_THE_GARBAGE_COLLECTOR].Rol(
	ID_Rol numeric(18,0) PRIMARY KEY IDENTITY(0,1),
	Nombre nvarchar(255) UNIQUE NOT NULL,
	Activo BIT DEFAULT 1 NOT NULL
)
go

INSERT INTO [WE_MISS_THE_GARBAGE_COLLECTOR].Rol(Nombre)
	VALUES
	('Administrador General'),
	('Administrador'),
	('Recepcionista'),
	('Guest')
	
/**************************************
			FUNCIONALIDADES
**************************************/

CREATE TABLE [WE_MISS_THE_GARBAGE_COLLECTOR].Funcionalidad(
	ID_Funcionalidad numeric(18,0) PRIMARY KEY IDENTITY(0,1),
	Descripcion nvarchar(255) UNIQUE not null
)
go

INSERT INTO [WE_MISS_THE_GARBAGE_COLLECTOR].Funcionalidad(Descripcion)
	VALUES
	('ABM DE ROL'),
	('ABM DE USUARIO'),
	('ABM DE CLIENTES'),
	('ABM DE HOTEL'),
	('ABM DE HABITACION'),
	('ABM DE REGIMEN DE ESTADIA'),
	('GENERAR/MODIFICAR RESERVA'),
	('CANCELAR RESERVA'),
	('REGISTRAR ESTADIA'),
	('REGISTRAR CONSUMIBLES'),
	('FACTURAR PUBLICACIONES'),
	('LISTADO ESTADISTICO')
	
/**************************************
		FUNCIONALIDAD DE CADA ROL
**************************************/

CREATE TABLE [WE_MISS_THE_GARBAGE_COLLECTOR].RolxFuncionalidad(
	Rol numeric(18,0) FOREIGN KEY REFERENCES [WE_MISS_THE_GARBAGE_COLLECTOR].ROL (ID_Rol) NOT NULL,
	Funcionalidad numeric(18,0) FOREIGN KEY REFERENCES [WE_MISS_THE_GARBAGE_COLLECTOR].Funcionalidad (ID_Funcionalidad) NOT NULL
	PRIMARY KEY(Rol,Funcionalidad)
)
go
INSERT INTO [WE_MISS_THE_GARBAGE_COLLECTOR].RolxFuncionalidad(Rol,Funcionalidad)
(
	SELECT R.ID_Rol,F.ID_Funcionalidad
	FROM [WE_MISS_THE_GARBAGE_COLLECTOR].Rol R,[WE_MISS_THE_GARBAGE_COLLECTOR].Funcionalidad F
	WHERE R.Nombre='Administrador General'
	OR (R.Nombre='Administrador' AND (F.Descripcion='ABM DE USUARIO' OR F.Descripcion='ABM DE HOTEL' OR F.Descripcion='ABM DE HABITACION' OR F.Descripcion='ABM DE REGIMEN DE ESTADIA'))
	OR (R.Nombre='Guest' AND (F.Descripcion='GENERAR/MODIFICAR RESERVA' OR F.Descripcion='CANCELAR RESERVA'))
	OR (R.Nombre='Recepcionista' AND (F.Descripcion='ABM DE CLIENTES' OR F.Descripcion='GENERAR/MODIFICAR RESERVA' OR 
	F.Descripcion='CANCELAR RESERVA' OR F.Descripcion='REGISTRAR ESTADIA' OR F.Descripcion='REGISTRAR CONSUMIBLES' OR 
	F.Descripcion='FACTURAR PUBLICACIONES'))
)
go

/**************************************
				PERSONA
**************************************/

CREATE TABLE [WE_MISS_THE_GARBAGE_COLLECTOR].Persona(
	ID_Persona numeric(18,0) PRIMARY KEY,
	Nombre nvarchar(255) NOT NULL,
	Apellido nvarchar(255) NOT NULL,
	Tipo_documento nvarchar(255),
	Nro_documento numeric(18,0) NOT NULL,
	Telefono numeric(18,0),
	Direccion nvarchar(255) NOT NULL,
	Fecha_Nac datetime
)

GO
/**************************************
				HOTELES
**************************************/

CREATE TABLE [WE_MISS_THE_GARBAGE_COLLECTOR].Hotel(
	ID_Hotel numeric(18,0) PRIMARY KEY IDENTITY(0,1),
	Habilitado bit default 1 NOT NULL,
	Nombre nvarchar(255) NOT NULL,
	Mail nvarchar(255),
	Telefono numeric(18,0),
	Calle nvarchar(255) NOT NULL,
	Cant_estrellas numeric(1,0) NOT NULL,
	Recargo_estrellas numeric(18,0) NOT NULL,
	Ciudad nvarchar(255) NOT NULL,
	Pais nvarchar(255),
	Fecha_creacion datetime,
	Admin_asignado bit default 1 NOT NULL
)

GO

/**************************************
				CLIENTE
**************************************/

CREATE TABLE [WE_MISS_THE_GARBAGE_COLLECTOR].Cliente(
	ID_Cliente numeric(18,0) PRIMARY KEY IDENTITY(0,1),
	Mail nvarchar(255) NOT NULL,
	Persona numeric(18,0) FOREIGN KEY REFERENCES [WE_MISS_THE_GARBAGE_COLLECTOR].Persona(ID_Persona) NOT NULL,
	Habilitado BIT DEFAULT 1 NOT NULL,
	Nacionalidad nvarchar(255) NOT NULL,
	Localidad nvarchar(255),
	PaisDeOrigen nvarchar(255),
	Piso numeric(18,0) NOT NULL,
	Depto nvarchar(50) NOT NULL
)
GO
 
/**************************************
				USUARIO
**************************************/

CREATE TABLE [WE_MISS_THE_GARBAGE_COLLECTOR].Usuario(
	ID_User numeric(18,0) PRIMARY KEY IDENTITY(0,1),
	Username nvarchar(255) UNIQUE,
	Persona numeric(18,0) FOREIGN KEY REFERENCES [WE_MISS_THE_GARBAGE_COLLECTOR].Persona(ID_Persona) NOT NULL,
	Habilitado BIT DEFAULT 1 NOT NULL,
	Intentos numeric(1,0) DEFAULT 0 NOT NULL,
	SHA256 nvarchar(255),
	Mail nvarchar(255) NOT NULL
)
GO

/**************************************
		ROLES DE CADA USUARIO
**************************************/

CREATE TABLE [WE_MISS_THE_GARBAGE_COLLECTOR].RolxUsuario(
	Rol numeric(18,0) FOREIGN KEY REFERENCES [WE_MISS_THE_GARBAGE_COLLECTOR].Rol (ID_Rol) NOT NULL,
	Usuario numeric(18,0) FOREIGN KEY REFERENCES [WE_MISS_THE_GARBAGE_COLLECTOR].Usuario (ID_User) NOT NULL,
	Hotel numeric(18,0) FOREIGN KEY REFERENCES [WE_MISS_THE_GARBAGE_COLLECTOR].Hotel(ID_Hotel),
	PRIMARY KEY (Rol,Usuario)
)
go

/**************************************
			SESIONES
**************************************/

CREATE TABLE [WE_MISS_THE_GARBAGE_COLLECTOR].UsuarioxHotel(
	Usuario numeric(18,0) FOREIGN KEY REFERENCES [WE_MISS_THE_GARBAGE_COLLECTOR].Usuario(ID_User) NOT NULL,
	Hotel numeric(18,0) FOREIGN KEY REFERENCES [WE_MISS_THE_GARBAGE_COLLECTOR].Hotel(ID_Hotel) NOT NULL,
	PRIMARY KEY(Usuario,Hotel)
)
go

/**************************************
			BAJAS HOTELES
**************************************/
CREATE TABLE [WE_MISS_THE_GARBAGE_COLLECTOR].Baja_Hotel(
	ID_Baja numeric(18,0) PRIMARY KEY IDENTITY(0,1),
	Hotel numeric(18,0) FOREIGN KEY REFERENCES [WE_MISS_THE_GARBAGE_COLLECTOR].Hotel(ID_Hotel) NOT NULL,
	Inicio_periodo datetime NOT NULL,
	Fin_periodo datetime NOT NULL,
	Motivo nvarchar(255) NOT NULL
)
go

/**************************************
			HABITACIONES
**************************************/
CREATE TABLE [WE_MISS_THE_GARBAGE_COLLECTOR].TipoDeHabitacion(
	Tipo_codigo numeric(18,0)  PRIMARY KEY NOT NULL,
	Tipo_porcentual numeric(18,2) NOT NULL,
	Tipo_descripcion nvarchar(255) NOT NULL,
)
GO

CREATE TABLE [WE_MISS_THE_GARBAGE_COLLECTOR].Habitacion(
	Numero numeric(18,0) NOT NULL,
	Hotel numeric(18,0) FOREIGN KEY REFERENCES [WE_MISS_THE_GARBAGE_COLLECTOR].Hotel (ID_Hotel) NOT NULL,
	Tipo numeric(18,0) FOREIGN KEY REFERENCES [WE_MISS_THE_GARBAGE_COLLECTOR].TipoDeHabitacion(Tipo_codigo) NOT NULL,
	Piso numeric(18,0) NOT NULL,
	Frente nvarchar(50) NOT NULL,
	Habilitado bit default 1 NOT NULL,
	Ocupada bit default 0 NOT NULL,
	Descripcion nvarchar(255) default 'Habitacion normal' NOT NULL,
	PRIMARY KEY(Numero,Hotel)
)
go

/**************************************
				REGIMENES
**************************************/

CREATE TABLE [WE_MISS_THE_GARBAGE_COLLECTOR].Regimen(
	Codigo numeric(18,0) PRIMARY KEY IDENTITY(0,1),
	Descripcion nvarchar(255) NOT NULL,
	Activo bit default 1 NOT NULL,
	Precio_base numeric(18,2) NOT NULL
	constraint CK_precio_base check (Precio_base>=0)
)
go

/**************************************
		REGIMEN DE CADA HOTEL
**************************************/

CREATE TABLE [WE_MISS_THE_GARBAGE_COLLECTOR].RegimenxHotel(
	Regimen numeric(18,0) FOREIGN KEY REFERENCES [WE_MISS_THE_GARBAGE_COLLECTOR].Regimen(Codigo) NOT NULL,
	Hotel numeric(18,0) FOREIGN KEY REFERENCES [WE_MISS_THE_GARBAGE_COLLECTOR].Hotel(ID_Hotel) NOT NULL
	PRIMARY KEY(Regimen,Hotel)
)
go

/**************************************
          FACTURACION
**************************************/

create table [WE_MISS_THE_GARBAGE_COLLECTOR].Factura(
	ID_Factura numeric(18,0) PRIMARY KEY,
	ID_Cliente numeric(18,0) FOREIGN KEY REFERENCES [WE_MISS_THE_GARBAGE_COLLECTOR].Cliente(ID_Cliente) NOT NULL,
	Fecha date,
	Forma_Pago nvarchar(255),
	Nro_Tarjeta numeric(20,0),
	Total numeric(18,2)
	constraint CK_Total check (Total>=0)
)
go

create table [WE_MISS_THE_GARBAGE_COLLECTOR].RenglonFactura(
	ID_Renglon numeric (18,0) identity (1,1) PRIMARY KEY,
	ID_Factura numeric(18,0) FOREIGN KEY REFERENCES [WE_MISS_THE_GARBAGE_COLLECTOR].Factura(ID_Factura),
	Descripcion nvarchar(255) NOT NULL,
	Item_Cantidad numeric(18,0),
	Item_Monto numeric(18,2)
	constraint CK_Item_Monto check (Item_Monto>=0)
)
go

/**************************************
          RESERVAS
**************************************/


create table [WE_MISS_THE_GARBAGE_COLLECTOR].Reserva(
	ID_Reserva numeric(18,0) PRIMARY KEY,
	Usuario numeric(18,0) FOREIGN KEY REFERENCES [WE_MISS_THE_GARBAGE_COLLECTOR].Usuario(ID_User),
	Fecha date,
	Inicio_Permanencia date,
	Fin_Permanencia date,
	Hotel numeric(18,0) NOT NULL,
	Regimen numeric(18,0) NOT NULL,
	Cliente numeric(18,0) FOREIGN KEY REFERENCES [WE_MISS_THE_GARBAGE_COLLECTOR].Cliente(ID_Cliente) NOT NULL,
	Estado int default 1 NOT NULL
)
go

ALTER TABLE [WE_MISS_THE_GARBAGE_COLLECTOR].Reserva
  ADD CONSTRAINT FK_Regimen_Reserva
  FOREIGN KEY(Regimen, Hotel) REFERENCES [WE_MISS_THE_GARBAGE_COLLECTOR].RegimenxHotel(Regimen, Hotel)
go

create table [WE_MISS_THE_GARBAGE_COLLECTOR].ReservaCancelada(
 
	Motivo nvarchar(255) NOT NULL,
	Fecha_Cancelacion date NOT NULL,
	Usuario numeric(18,0) FOREIGN KEY REFERENCES [WE_MISS_THE_GARBAGE_COLLECTOR].Usuario(ID_User),
	Reserva numeric(18,0) FOREIGN KEY REFERENCES [WE_MISS_THE_GARBAGE_COLLECTOR].Reserva(ID_Reserva) NOT NULL,
	Cliente numeric(18,0) FOREIGN KEY REFERENCES [WE_MISS_THE_GARBAGE_COLLECTOR].Cliente(ID_Cliente)
)
go

/**************************************
         HABITACIONES RESERVADAS
**************************************/
CREATE TABLE [WE_MISS_THE_GARBAGE_COLLECTOR].HabitacionxReserva(
	Habitacion numeric(18,0) NOT NULL,
	Hotel numeric(18,0) NOT NULL,
	Reserva numeric(18,0) FOREIGN KEY REFERENCES [WE_MISS_THE_GARBAGE_COLLECTOR].Reserva(ID_Reserva)
	PRIMARY KEY(Habitacion,Hotel,Reserva)
)

ALTER TABLE [WE_MISS_THE_GARBAGE_COLLECTOR].HabitacionxReserva
  ADD CONSTRAINT FK_HabitacionxReserva
  FOREIGN KEY(Habitacion, Hotel) REFERENCES [WE_MISS_THE_GARBAGE_COLLECTOR].Habitacion(Numero,Hotel)

GO
/**************************************
              ESTADIA
**************************************/

create table [WE_MISS_THE_GARBAGE_COLLECTOR].Estadia(
	ID_Estadia numeric(18,0) IDENTITY(0,1) PRIMARY KEY,
	Fecha_Ingreso date,
	Fecha_Egreso date,
	Habitacion numeric(18,0) NOT NULL,
	Hotel numeric(18,0) NOT NULL,
	Reserva numeric(18,0) NOT NULL
)
go

ALTER TABLE [WE_MISS_THE_GARBAGE_COLLECTOR].Estadia
  ADD CONSTRAINT FK_Estadia_HabitacionxReserva
  FOREIGN KEY(Habitacion, Hotel, Reserva) REFERENCES [WE_MISS_THE_GARBAGE_COLLECTOR].HabitacionxReserva(Habitacion, Hotel, Reserva)
go

/**************************************
          CONSUMIBLES
**************************************/

create table [WE_MISS_THE_GARBAGE_COLLECTOR].Consumible(
	ID_Consumible numeric(18,0) PRIMARY KEY,
	Descripcion nvarchar(255) NOT NULL,
	Precio numeric(18,2)
	constraint CK_precio check (Precio>=0)
)
go

/**************************************
        CONSUMIBLES POR ESTADIA
**************************************/
CREATE TABLE [WE_MISS_THE_GARBAGE_COLLECTOR].ConsumiblexEstadia(
	Consumible numeric(18,0) FOREIGN KEY REFERENCES [WE_MISS_THE_GARBAGE_COLLECTOR].Consumible(ID_Consumible),
	Estadia numeric(18,0) FOREIGN KEY REFERENCES [WE_MISS_THE_GARBAGE_COLLECTOR].Estadia(ID_Estadia)
)
GO

--CREACION USUARIO ADMIN
INSERT INTO [WE_MISS_THE_GARBAGE_COLLECTOR].Persona(ID_Persona ,Nombre,Apellido,Tipo_documento,Nro_Documento,Telefono,Direccion,Fecha_Nac)
VALUES(0,'admin','apellido','DNI',0,0,'Medrano 951',0)
GO

DECLARE @hash nvarchar(255)
SET @hash =  'e6b87050bfcb8143fcb8db0170a4dc9ed00d904ddd3e2a4ad1b1e8dc0fdc9be7'
INSERT INTO WE_MISS_THE_GARBAGE_COLLECTOR.Usuario(Username,Persona,SHA256,Mail)
VALUES
	('admin',0, @hash,'admin@gmail.com')
GO

INSERT INTO [WE_MISS_THE_GARBAGE_COLLECTOR].RolxUsuario(Rol,Usuario)
	VALUES
		(0,0) 

GO
/******************************************************************
			STORED PROCEDURES, TRIGGERS Y FUNCTIONS
*******************************************************************/
--FUNCIONES AUXILIARES
CREATE FUNCTION [WE_MISS_THE_GARBAGE_COLLECTOR].DevolvemeTipoDeDocumento(@Nacionalidad nvarchar(255))
RETURNS nvarchar(255)
BEGIN
	DECLARE @Tipo_doc nvarchar(255)
	SET @Tipo_doc='DNI'
	IF (@Nacionalidad!='ARGENTINO')
		SET @Tipo_doc='Pasaporte'
	RETURN @Tipo_doc
END

GO

CREATE FUNCTION [WE_MISS_THE_GARBAGE_COLLECTOR].DameDireccion(@Dominio nvarchar(255),@Nro numeric(18,0))
RETURNS nvarchar(255)
BEGIN
	DECLARE @Nro_calle nvarchar(255)
	SET @Nro_calle=CAST(@Nro AS nvarchar(255))
	RETURN @Dominio+' '+@Nro_calle
END
GO

CREATE FUNCTION [WE_MISS_THE_GARBAGE_COLLECTOR].cantidadPersonas(@tipoHabitacion varchar(255))
RETURNS int
BEGIN
RETURN(
	CASE @tipoHabitacion
	WHEN 'Base Simple' THEN 1
	WHEN 'Base Doble' THEN  2
	WHEN 'Base Triple' THEN  3
	WHEN 'Base Cuadruple' THEN 4
	WHEN 'King' THEN 5
	END
)
END
GO
--STORED PROCEDURE PARA LA MODIFICACION DE UN CLIENTE
CREATE PROCEDURE [WE_MISS_THE_GARBAGE_COLLECTOR].[SP_ModificacionCliente] 
@idCliente numeric(18,0),
@idPersona numeric(18,0),
@mail nvarchar(255),
@habilitado bit,
@nacionalidad nvarchar(255),
@localidad nvarchar(255),
@paisOrigen nvarchar(255),
@piso numeric(18,0),
@depto nvarchar(50),
@nombre nvarchar(255),
@apellido nvarchar(255),
@tipoDocumento nvarchar(255),
@nroDocumento numeric(18,0),
@telefono numeric(18,0),
@direccion nvarchar(255),
@fechaNac datetime
AS
	UPDATE [WE_MISS_THE_GARBAGE_COLLECTOR].Cliente
	SET 
		Mail=@mail,
		Habilitado=@habilitado,
		Nacionalidad=@nacionalidad,
		Localidad=@localidad,
		PaisDeOrigen=@paisOrigen,
		Piso=@piso,
		Depto=@depto
	WHERE ID_Cliente=@idCliente
	UPDATE [WE_MISS_THE_GARBAGE_COLLECTOR].Persona
	SET Nombre=@nombre,
		Apellido=@apellido,
		Tipo_documento=@tipoDocumento,
		Nro_documento=@nroDocumento,
		Telefono=@telefono,
		Direccion=@direccion,
		Fecha_Nac=@fechaNac
	WHERE ID_Persona=@idPersona
GO
--STORED PROCEDURE PARA ELIMINAR(INHABILITAR) UN CLIENTE
CREATE PROCEDURE [WE_MISS_THE_GARBAGE_COLLECTOR].[SP_BajaCliente]
@id_cliente numeric(18,0)
AS
	UPDATE [WE_MISS_THE_GARBAGE_COLLECTOR].Cliente
	SET Habilitado=0
	WHERE ID_Cliente=@id_cliente
GO
--STORED PROCEDURES PARA ASIGNAR HOTEL Y ROL A USUARIO
CREATE PROCEDURE [WE_MISS_THE_GARBAGE_COLLECTOR].[SP_insert_rolxusuario]
@id_rol numeric(18,0),
@id_user numeric(18,0),
@id_hotel numeric(18,0)
AS
	INSERT INTO [WE_MISS_THE_GARBAGE_COLLECTOR].RolxUsuario(Rol,Usuario,Hotel)
	VALUES
		(@id_rol,@id_user,@id_hotel)
GO
CREATE PROCEDURE [WE_MISS_THE_GARBAGE_COLLECTOR].[SP_insert_usuarioxhotel]
@id_hotel numeric(18,0),
@id_user numeric(18,0)
AS
	INSERT INTO [WE_MISS_THE_GARBAGE_COLLECTOR].UsuarioxHotel(Hotel,Usuario)
	VALUES
	(@id_hotel,@id_user)
GO
--STORED PROCEDURE PARA MODIFICACION DE UN USUARIO
CREATE PROCEDURE [WE_MISS_THE_GARBAGE_COLLECTOR].[SP_modificacion_usuario]
@id_user numeric(18,0),
@id_persona numeric(18,0),
@username nvarchar(255),
@apellido nvarchar(255),
@nombre nvarchar(255),
@nro_documento numeric(18,0),
@tipo_documento nvarchar(255),
@mail nvarchar(255),
@direccion nvarchar(255),
@telefono numeric(18,0),
@fecha_nac nvarchar(255),
@hotel_removed numeric(18,0),
@hotel_added numeric(18,0),
@rol_removed numeric(18,0),
@rol_added numeric(18,0)
AS
	UPDATE [WE_MISS_THE_GARBAGE_COLLECTOR].Persona
	SET Apellido=@apellido,Nombre=@nombre,Nro_documento=@nro_documento,Tipo_documento=@tipo_documento,Direccion=@direccion,Telefono=@telefono,Fecha_Nac=@fecha_nac
	WHERE ID_Persona=@id_persona
	
	UPDATE [WE_MISS_THE_GARBAGE_COLLECTOR].Usuario
	SET Username=@username,Mail=@mail
	WHERE ID_User=@id_user
	
	IF (@hotel_removed is not null)
	BEGIN
		DELETE
		FROM [WE_MISS_THE_GARBAGE_COLLECTOR].UsuarioxHotel
		WHERE Usuario=@id_user
	END
	
	IF (@hotel_added is not null)
	BEGIN
		INSERT INTO [WE_MISS_THE_GARBAGE_COLLECTOR].UsuarioxHotel(Usuario,Hotel)
		VALUES
			(@id_user,@hotel_added)			
	END
	
	IF (@hotel_removed is not null)
	BEGIN
		DELETE
		FROM [WE_MISS_THE_GARBAGE_COLLECTOR].UsuarioxHotel
		WHERE Usuario=@id_user
	END
	
	IF (@rol_removed is not null)
	BEGIN
		DELETE
		FROM [WE_MISS_THE_GARBAGE_COLLECTOR].RolxUsuario
		WHERE Usuario=@id_user
	END
	
	IF (@rol_added is not null)
	BEGIN
		INSERT INTO [WE_MISS_THE_GARBAGE_COLLECTOR].RolxUsuario
		VALUES
			(@rol_added,@id_user,@hotel_added)
	END
GO

--STORED PROCEDURE PARA OBTENER HABITACIONES SEGUN PEDIDO EN RESERVA
CREATE PROCEDURE [WE_MISS_THE_GARBAGE_COLLECTOR].[SP_Disponibilidad_Pedido]
@fecha_desde datetime,
@fecha_hasta datetime,
@tipo_habitacion numeric(18,0),
@cant_habitaciones int,
@hotel numeric(18,0)
AS
		SELECT TOP (@cant_habitaciones)HAB.*
		FROM [WE_MISS_THE_GARBAGE_COLLECTOR].Habitacion HAB
		WHERE 
				HAB.Tipo=@tipo_habitacion 
				AND HAB.Hotel =@hotel 
				AND HAB.Numero NOT IN (
										SELECT DISTINCT HXR.Habitacion 
										FROM [WE_MISS_THE_GARBAGE_COLLECTOR].Reserva R
										JOIN [WE_MISS_THE_GARBAGE_COLLECTOR].HabitacionxReserva HXR ON R.ID_Reserva=HXR.Reserva
										AND HXR.Hotel =@hotel 
										JOIN [WE_MISS_THE_GARBAGE_COLLECTOR].Habitacion H ON H.Hotel =HXR.Hotel
										AND H.Numero =HXR.Habitacion
										AND H.Tipo =@tipo_habitacion 
										WHERE --R.Estado =1 OR R.Estado =2 OR R.Estado =6
										(R.Inicio_Permanencia <=@fecha_desde AND @fecha_desde<=R.Fin_Permanencia )
										AND (R.Inicio_Permanencia  <=@fecha_hasta AND @fecha_hasta<=R.Fin_Permanencia)
				)
		
	
GO
--STORED PROCEDURE PARA OBTENER CANTIDAD DE PERSONAS
--SEGUN CODIGO DE TIPO DE HABITACION
CREATE PROCEDURE [WE_MISS_THE_GARBAGE_COLLECTOR].[SP_Obtener_Cant_Personas]
@tipo_habitacion numeric(18,0)
AS
	DECLARE @tipo_descripcion nvarchar(255)
	
	SELECT @tipo_descripcion=Tipo_descripcion 
	FROM [WE_MISS_THE_GARBAGE_COLLECTOR].TipoDeHabitacion
	WHERE @tipo_habitacion =Tipo_codigo 
	
	RETURN [WE_MISS_THE_GARBAGE_COLLECTOR].cantidadPersonas(@tipo_descripcion)
GO
--STORED PROCEDURE PARA OBTENER TIPO_PORCENTUAL
--A PARTIR DEL TIPO_CODIGO DE UNA HABITACION
CREATE PROCEDURE [WE_MISS_THE_GARBAGE_COLLECTOR].[SP_Obtener_Tipo_Porcentual]
@tipo_habitacion numeric(18,0)
AS
	DECLARE @tipo_porcentual numeric(18,2)

	SELECT @tipo_porcentual =Tipo_porcentual 
	FROM [WE_MISS_THE_GARBAGE_COLLECTOR].TipoDeHabitacion
	WHERE Tipo_codigo =@tipo_habitacion
	
	RETURN @tipo_porcentual

GO
--STORED PROCEDURE QUE OBTIENE EL ULTIMO ID DE RESERVA
CREATE PROCEDURE [WE_MISS_THE_GARBAGE_COLLECTOR].[SP_Ultimo_ID_Reserva]
AS
	RETURN(
			SELECT TOP 1 ID_Reserva
			FROM [WE_MISS_THE_GARBAGE_COLLECTOR].Reserva
			ORDER BY ID_Reserva DESC
	)
GO

--STORED PROCEDURE PARA LA GENERACION DE RESERVAS HECHAS POR CLIENTES
CREATE PROCEDURE [WE_MISS_THE_GARBAGE_COLLECTOR].[SP_Generar_Reserva_Por_Cliente]
@codigo numeric(18,0),
@fecha_inicio datetime,
@fecha_fin datetime,
@hotel numeric(18,0),
@cliente numeric(18,0),
@regimen numeric(18,0)
AS
	INSERT INTO [WE_MISS_THE_GARBAGE_COLLECTOR].Reserva(ID_Reserva,Fecha,Inicio_Permanencia,Fin_Permanencia,Hotel,Cliente,Regimen)
	VALUES(
			@codigo,
			GETDATE(),
			@fecha_inicio,	
			@fecha_fin,
			@hotel ,
			@cliente,
			@regimen
	)
	RETURN @codigo
GO

--STORED PROCEDURE PARA LA GENERACION DE RESERVAS HECHAS POR USUARIOS
CREATE PROCEDURE [WE_MISS_THE_GARBAGE_COLLECTOR].[SP_Generar_Reserva_Por_Usuario]
@usuario numeric(18,0),
@codigo numeric(18,0),
@fecha_inicio datetime,
@fecha_fin datetime,
@hotel numeric(18,0),
@cliente numeric(18,0),
@regimen numeric(18,0)
AS
	INSERT INTO [WE_MISS_THE_GARBAGE_COLLECTOR].Reserva(Usuario,ID_Reserva,Fecha,Inicio_Permanencia,Fin_Permanencia,Hotel,Cliente,Regimen)
	VALUES(
			@usuario,
			@codigo,
			GETDATE(),
			@fecha_inicio,	
			@fecha_fin,
			@hotel ,
			@cliente,
			@regimen
	)
	RETURN @codigo
GO
--STORED PROCEDURE PARA CANCELAR RESERVA
CREATE PROCEDURE [WE_MISS_THE_GARBAGE_COLLECTOR].[SP_Cancelar_Reserva]
@tipo_user nvarchar(255),
@nro_reserva numeric(18,0),
@motivo nvarchar(255),
@fecha_cancelacion datetime,
@usuario numeric(18,0)
AS
	DECLARE @inicio_reserva datetime
	DECLARE @estado bit
	DECLARE @codigo numeric(18,0)

	SELECT @inicio_reserva=Inicio_Permanencia,@estado=Estado,@codigo=ID_Reserva 
	FROM [WE_MISS_THE_GARBAGE_COLLECTOR].Reserva
	WHERE ID_Reserva =@nro_reserva
	
	IF(@codigo is null)
	BEGIN
		raiserror('La reserva no existe',13,1)
		RETURN -1
	END
	
	IF (@fecha_cancelacion >@inicio_reserva)--CAMBIAR ESTA MAL PORQUE ES UN 1 DIA ANTES
	BEGIN
		raiserror('La reserva solo puede cancelarse hasta un dia antes de la fecha de inicio de la reserva',11,1)
		RETURN -2
	END
	
	IF(@estado =6)
	BEGIN
		raiserror('La reserva ya ha sido utilizada',12,1)
		RETURN -3
	END
	
	IF(@tipo_user  ='usuario')
	BEGIN
		INSERT INTO [WE_MISS_THE_GARBAGE_COLLECTOR].ReservaCancelada(Motivo,Fecha_Cancelacion,Usuario,Reserva )
		VALUES
			(	@motivo,
				@fecha_cancelacion,
				@usuario,
				@nro_reserva
			)
		
		UPDATE [WE_MISS_THE_GARBAGE_COLLECTOR].Reserva
		SET	Estado =3
		WHERE ID_Reserva =@codigo
		RETURN @codigo
	END
	
	IF(@tipo_user ='cliente')
	BEGIN
		INSERT INTO [WE_MISS_THE_GARBAGE_COLLECTOR].ReservaCancelada(Motivo,Fecha_Cancelacion,Cliente ,Reserva )
		VALUES
			(	@motivo,
				@fecha_cancelacion,
				@usuario,
				@nro_reserva
			)

		UPDATE [WE_MISS_THE_GARBAGE_COLLECTOR].Reserva
		SET	Estado =4
		WHERE ID_Reserva =@codigo
		
		RETURN @codigo
	END
	
GO
------------------------------------------------------------------
--             STORED PROCEDURE PARA LOGIN 
------------------------------------------------------------------
-- Incrementa o resetea la cantidad de intentos fallidos
--        y deshabilita la cuenta si supero los 3.
------------------------------------------------------------------

CREATE PROCEDURE [WE_MISS_THE_GARBAGE_COLLECTOR].[LOGIN](@Username nvarchar(255),@SHA256 nvarchar(255)) 
AS
	DECLARE @U_name nvarchar(255)
	DECLARE @Id numeric(18,0)
	DECLARE @U_SHA256  nvarchar(255)
	DECLARE @U_habilitado bit
	DECLARE @Cant_intentos int
	--Busqueda de usuario
	SELECT @Id=ID_User,@U_name=Username,@U_SHA256=SHA256,@U_habilitado=Habilitado,@Cant_intentos=Intentos
	FROM [WE_MISS_THE_GARBAGE_COLLECTOR].Usuario
	WHERE Username=@Username
	
	IF (@U_name IS NULL) BEGIN
		raiserror('No existe ningun usuario con ese username.', 11, 1)
		RETURN -1 
	END	
 
	IF (@U_habilitado=0) BEGIN
		raiserror('El usuario se encuentra desahabilitado',16,1)
		RETURN -2
	END
	--Caso de contraseña incorrecta
	IF (@U_SHA256<>@SHA256) 
	BEGIN
		SET @Cant_intentos=@Cant_intentos+1
		IF  @Cant_intentos=3 
		BEGIN	--Si el usuario llega al max de intentos
			SET @U_habilitado=0		--queda deshabilitado
			SET @Cant_intentos=0 
		END
		UPDATE [WE_MISS_THE_GARBAGE_COLLECTOR].Usuario
		SET Intentos=@Cant_intentos,Habilitado=@U_habilitado
		WHERE Username=@Username
		raiserror('Contraseña incorrecta',16,1)
		RETURN -3 
	END
	
	--Contraseña correcta.
	IF(@U_SHA256=@SHA256)
	BEGIN
		IF (@Cant_intentos<> 0)--Si hubo intentos fallidos, reseteo el contador.
			UPDATE [WE_MISS_THE_GARBAGE_COLLECTOR].Usuario SET Intentos = 0 WHERE Username = @Username 
		RETURN @Id --Retorno id del usuario.
	END
GO

------------------------------------------------------------------
--					MIGRACION HOTELES 
------------------------------------------------------------------

INSERT INTO [WE_MISS_THE_GARBAGE_COLLECTOR].Hotel(Nombre ,Calle,Cant_estrellas,Ciudad,Recargo_estrellas)
(
	SELECT DISTINCT 'Hotel '+Hotel_Calle ,[WE_MISS_THE_GARBAGE_COLLECTOR].DameDireccion(Hotel_Calle,Hotel_Nro_Calle),Hotel_CantEstrella,Hotel_Ciudad,Hotel_Recarga_Estrella 
	FROM [gd_esquema].[Maestra]
)

GO

------------------------------------------------------------------
--					MIGRACION CLIENTES 
------------------------------------------------------------------
GO
--Cursor para la migracion de Clientes
DECLARE @apellido nvarchar(255)
DECLARE @depto nvarchar(50)
DECLARE @piso numeric(18,0)
DECLARE @calle_nombre nvarchar(255)
DECLARE @fecha_nac datetime
DECLARE @mail nvarchar(255)
DECLARE @nacionalidad nvarchar(255)
DECLARE @nombre nvarchar(255)
DECLARE @calle_nro numeric(18,0)
DECLARE @dni numeric(18,0)
DECLARE @direccion nvarchar(255)
DECLARE @tipo_doc nvarchar(255)
DECLARE @Contador int
SET @Contador=1

DECLARE CursorClientes CURSOR FOR 
	SELECT DISTINCT Cliente_Apellido,Cliente_Depto,Cliente_Piso,Cliente_Dom_Calle,Cliente_Fecha_Nac,
				Cliente_Mail,Cliente_Nacionalidad,Cliente_Nombre,Cliente_Nro_Calle,Cliente_Pasaporte_Nro 
	FROM [GD2C2014].[gd_esquema].[Maestra]

OPEN CursorClientes

FETCH NEXT FROM CursorClientes
INTO @apellido,@depto,@piso,@calle_nombre,@fecha_nac,@mail,@nacionalidad,@nombre,@calle_nro,@dni

WHILE (@@FETCH_STATUS=0)
	BEGIN
		SET @direccion=[WE_MISS_THE_GARBAGE_COLLECTOR].DameDireccion(@calle_nombre,@calle_nro)
		SET @tipo_doc=[WE_MISS_THE_GARBAGE_COLLECTOR].DevolvemeTipoDeDocumento(@nacionalidad)
		INSERT INTO [WE_MISS_THE_GARBAGE_COLLECTOR].Persona(ID_Persona,Nombre,Apellido,Tipo_documento,Nro_documento,Direccion,Fecha_Nac)
			VALUES
			(@Contador,@nombre,@apellido,@tipo_doc,@dni,@direccion,@fecha_nac)
		INSERT INTO [WE_MISS_THE_GARBAGE_COLLECTOR].Cliente(Mail,Nacionalidad,Persona,Depto,Piso)
			VALUES
			(@mail,@nacionalidad,@Contador,@depto,@piso)
		SET @Contador=@Contador+1
		FETCH NEXT FROM CursorClientes
		INTO @apellido,@depto,@piso,@calle_nombre,@fecha_nac,@mail,@nacionalidad,@nombre,@calle_nro,@dni
	END
CLOSE CursorClientes
DEALLOCATE CursorClientes

GO
------------------------------------------------------------------
--					MIGRACION REGIMENES 
------------------------------------------------------------------
INSERT INTO [WE_MISS_THE_GARBAGE_COLLECTOR].Regimen(Descripcion,Precio_base)
	SELECT DISTINCT Regimen_Descripcion,Regimen_Precio 
	FROM gd_esquema.Maestra
GO
INSERT INTO [WE_MISS_THE_GARBAGE_COLLECTOR].RegimenxHotel
	SELECT DISTINCT R.Codigo,H.ID_Hotel
	FROM gd_esquema.Maestra M
	JOIN [WE_MISS_THE_GARBAGE_COLLECTOR].Regimen R ON R.Descripcion=M.Regimen_Descripcion
	AND R.Precio_base=M.Regimen_Precio
	JOIN [WE_MISS_THE_GARBAGE_COLLECTOR].Hotel H ON H.Ciudad=M.Hotel_Ciudad
	AND H.Calle=[WE_MISS_THE_GARBAGE_COLLECTOR].DameDireccion(M.Hotel_Calle,M.Hotel_Nro_Calle)
	AND H.Ciudad=M.Hotel_Ciudad
	
GO
------------------------------------------------------------------
--					MIGRACION RESERVAS 
------------------------------------------------------------------
INSERT INTO [WE_MISS_THE_GARBAGE_COLLECTOR].Reserva(ID_Reserva,Usuario,Fecha,Inicio_Permanencia,Fin_Permanencia,Hotel,Regimen,Cliente)
	SELECT DISTINCT Reserva_Codigo,0,Reserva_Fecha_Inicio,Reserva_Fecha_Inicio,DATEADD(day,Reserva_Cant_Noches, Reserva_Fecha_Inicio),H.ID_Hotel,R.Codigo ,C.ID_Cliente
	FROM gd_esquema.Maestra M
	JOIN [WE_MISS_THE_GARBAGE_COLLECTOR].Hotel H ON H.Ciudad=M.Hotel_Ciudad
	AND H.Calle=[WE_MISS_THE_GARBAGE_COLLECTOR].DameDireccion(M.Hotel_Calle,M.Hotel_Nro_Calle)
	JOIN [WE_MISS_THE_GARBAGE_COLLECTOR].Regimen R ON R.Descripcion=M.Regimen_Descripcion
	AND R.Precio_base=M.Regimen_Precio
	JOIN [WE_MISS_THE_GARBAGE_COLLECTOR].RegimenxHotel RxH ON RxH.Hotel=H.ID_Hotel
	and RxH.Regimen=R.Codigo
	JOIN [WE_MISS_THE_GARBAGE_COLLECTOR].Cliente C ON C.Mail=M.Cliente_Mail
	AND C.Nacionalidad=M.Cliente_Nacionalidad
	JOIN [WE_MISS_THE_GARBAGE_COLLECTOR].Persona P ON P.ID_Persona=C.Persona
	AND P.Apellido=M.Cliente_Apellido
	AND P.Nombre=M.Cliente_Nombre
	AND P.Nro_documento=M.Cliente_Pasaporte_Nro
	AND P.Fecha_Nac=M.Cliente_Fecha_Nac
	AND P.Direccion=[WE_MISS_THE_GARBAGE_COLLECTOR].DameDireccion(M.Cliente_Dom_Calle,M.Cliente_Nro_Calle)
GO
------------------------------------------------------------------
--					MIGRACION HABITACIONES 
------------------------------------------------------------------
	
INSERT INTO [WE_MISS_THE_GARBAGE_COLLECTOR].TipoDeHabitacion(Tipo_codigo,Tipo_porcentual,Tipo_descripcion)
	SELECT DISTINCT Habitacion_Tipo_Codigo,Habitacion_Tipo_Porcentual,Habitacion_Tipo_Descripcion
	FROM gd_esquema.Maestra M
GO

INSERT INTO [WE_MISS_THE_GARBAGE_COLLECTOR].Habitacion(Numero,Hotel,Tipo,Piso,Frente)
	SELECT DISTINCT M.Habitacion_Numero,H.ID_Hotel,M.Habitacion_Tipo_Codigo,M.Habitacion_Piso,M.Habitacion_Frente
	FROM gd_esquema.Maestra M
	JOIN [WE_MISS_THE_GARBAGE_COLLECTOR].Hotel H ON M.Hotel_Ciudad=H.Ciudad
	AND [WE_MISS_THE_GARBAGE_COLLECTOR].DameDireccion(M.Hotel_Calle,M.Hotel_Nro_Calle)=H.Calle
GO

--HABITACIONES RESERVADAS
INSERT INTO [WE_MISS_THE_GARBAGE_COLLECTOR].HabitacionxReserva
	SELECT DISTINCT M.Habitacion_Numero,HOT.ID_Hotel,M.Reserva_Codigo
	FROM gd_esquema.Maestra M
	JOIN [WE_MISS_THE_GARBAGE_COLLECTOR].Habitacion HAB ON HAB.Numero=M.Habitacion_Numero
	JOIN [WE_MISS_THE_GARBAGE_COLLECTOR].Reserva R ON R.ID_Reserva=M.Reserva_Codigo
	JOIN [WE_MISS_THE_GARBAGE_COLLECTOR].Hotel HOT ON HOT.Ciudad=M.Hotel_Ciudad
	AND HOT.Calle=[WE_MISS_THE_GARBAGE_COLLECTOR].DameDireccion(M.Hotel_Calle,M.Hotel_Nro_Calle)
GO	
------------------------------------------------------------------
--					MIGRACION ESTADIA
------------------------------------------------------------------
INSERT INTO [WE_MISS_THE_GARBAGE_COLLECTOR].Estadia(Fecha_Ingreso,Fecha_Egreso,Habitacion,Hotel,Reserva)
	SELECT DISTINCT Estadia_Fecha_Inicio,DATEADD(DAY,Estadia_Cant_Noches,Estadia_Fecha_Inicio),M.Habitacion_Numero,HxR.Hotel,M.Reserva_Codigo
	FROM gd_esquema.Maestra M
	JOIN [WE_MISS_THE_GARBAGE_COLLECTOR].HabitacionxReserva HxR ON HxR.Habitacion=M.Habitacion_Numero
	AND HxR.Reserva=M.Reserva_Codigo
	JOIN [WE_MISS_THE_GARBAGE_COLLECTOR].Hotel H ON H.ID_Hotel=HxR.Hotel
	AND H.Ciudad=M.Hotel_Ciudad
	AND H.Calle=[WE_MISS_THE_GARBAGE_COLLECTOR].DameDireccion(M.Hotel_Calle,M.Hotel_Nro_Calle)
	WHERE Estadia_Fecha_Inicio is not null AND Estadia_Cant_Noches is not null
GO
------------------------------------------------------------------
--					MIGRACION CONSUMIBLES 
------------------------------------------------------------------
INSERT INTO [WE_MISS_THE_GARBAGE_COLLECTOR].Consumible(ID_Consumible,Descripcion, Precio)

	SELECT DISTINCT Consumible_Codigo,Consumible_Descripcion,Consumible_Precio
	FROM gd_esquema.Maestra
	WHERE Consumible_Codigo is not null
GO
	
INSERT INTO [WE_MISS_THE_GARBAGE_COLLECTOR].ConsumiblexEstadia
	SELECT M.Consumible_Codigo,E.ID_Estadia
	FROM gd_esquema.Maestra M
	JOIN [WE_MISS_THE_GARBAGE_COLLECTOR].Consumible C ON C.ID_Consumible=M.Consumible_Codigo
	JOIN [WE_MISS_THE_GARBAGE_COLLECTOR].Estadia E ON E.Habitacion=M.Habitacion_Numero
	AND E.Reserva=M.Reserva_Codigo
	WHERE (Estadia_Cant_Noches is not null) AND (Estadia_Fecha_Inicio is not null)
	AND (M.Consumible_Codigo is not null)
GO
------------------------------------------------------------------
--					MIGRACION FACTURAS 
------------------------------------------------------------------
INSERT INTO [WE_MISS_THE_GARBAGE_COLLECTOR].Factura(ID_Factura, ID_Cliente, Fecha, Forma_Pago, Nro_Tarjeta, Total)

	SELECT Factura_Nro,C.ID_Cliente,Factura_Fecha,'efectivo',0,Factura_Total
	FROM gd_esquema.Maestra M
	JOIN [WE_MISS_THE_GARBAGE_COLLECTOR].Cliente C ON C.Mail=M.Cliente_Mail
	AND C.Nacionalidad=M.Cliente_Nacionalidad
	JOIN [WE_MISS_THE_GARBAGE_COLLECTOR].Persona P ON P.ID_Persona=C.Persona
	AND P.Apellido=M.Cliente_Apellido
	AND P.Nombre=M.Cliente_Nombre
	AND P.Fecha_Nac=M.Cliente_Fecha_Nac
	AND P.Direccion=[WE_MISS_THE_GARBAGE_COLLECTOR].DameDireccion(M.Cliente_Dom_Calle,M.Cliente_Nro_Calle)
	AND P.Nro_documento=M.Cliente_Pasaporte_Nro
	WHERE Factura_Nro is not null
	GROUP BY Factura_Nro,C.ID_Cliente, Factura_Fecha, Factura_Total
	HAVING Factura_Nro is not null
	ORDER BY Factura_Nro
GO
------------------------------------------------------------------------------------------
--Renglones
INSERT INTO [WE_MISS_THE_GARBAGE_COLLECTOR].RenglonFactura(ID_Factura, Descripcion, Item_Cantidad, Item_Monto)
	SELECT Factura_Nro,Consumible_Descripcion, Item_Factura_Cantidad, Item_Factura_Monto
	FROM gd_esquema.Maestra
	WHERE (Factura_Nro is not null) AND (Consumible_Codigo is not null) AND (Regimen_Descripcion NOT LIKE 'All inclusive')

	UNION

	SELECT Factura_Nro,
	'Cant dias efectivamente alojados:Costo por dias no alojado',
	Estadia_Cant_Noches ,
	(Reserva_Cant_Noches-Estadia_Cant_Noches)*(Regimen_Precio*[WE_MISS_THE_GARBAGE_COLLECTOR].cantidadPersonas(Habitacion_Tipo_Descripcion))+Hotel_Recarga_Estrella
	FROM gd_esquema.Maestra
	WHERE (Factura_Nro is not null) AND (Reserva_Codigo is not null) AND (Estadia_Cant_Noches < Reserva_Cant_Noches)
	AND (Estadia_Cant_Noches is not null)
	
	UNION
	
	SELECT 
	Factura_Nro, 'Estadia', Estadia_Cant_Noches,Estadia_Cant_Noches*(Regimen_Precio*[WE_MISS_THE_GARBAGE_COLLECTOR].cantidadPersonas(Habitacion_Tipo_Descripcion))+Hotel_Recarga_Estrella
	FROM gd_esquema.Maestra
	WHERE (Factura_Nro is not null) AND (Reserva_Codigo is not null) AND (Estadia_Cant_Noches = Reserva_Cant_Noches)
	AND Estadia_Cant_Noches is not null
	
	UNION
	
	SELECT Factura_Nro,'Reserva','0',Reserva_Cant_Noches*(Regimen_Precio*[WE_MISS_THE_GARBAGE_COLLECTOR].cantidadPersonas(Habitacion_Tipo_Descripcion))+Hotel_Recarga_Estrella
	FROM gd_esquema.Maestra 
	WHERE (Factura_Nro is not null) AND (Reserva_Codigo is not null) AND (Estadia_Cant_Noches is null)
	
	ORDER BY Factura_Nro